import pandas as pd
from access_manager import AccessDBManager
from database_manager import SQLiteDBManager
import config as cfg
import datetime

now = datetime.datetime.now()
hour = now.hour + now.minute / 60
today=datetime.datetime.today()
fnow_time=now.strftime("%Y-%m-%d %H:%M:%S")
now_time=now.strftime("%H:%M:%S")
now_times=now.strftime("%Y%m%d")

 # 编写SQL查询语句
if hour<8.5:#日期，班次
    today=today-datetime.timedelta(days=1)
    date1=today.strftime("%Y-%#m-%#d")
    shift="N"
elif hour>20.5:
    date1=today.strftime("%Y-%#m-%#d")
    shift="N"
else:
    date1=today.strftime("%Y-%#m-%#d")
    shift="D"
print(date1,shift)
# 初始化数据库管理器
db_manager = SQLiteDBManager(cfg.db_path)
if db_manager.connect():
    try:
        # 读取数据到 DataFrame
        df = pd.read_sql_query(cfg.getdb_all_sql, db_manager.conn)
        # 处理数据，假设这里有 30 条线
        # hour=21
        for i in range(len(df)):
            try:
                id_value = df["id"][i]#获取id
                line = df["line"][i]#获取线
                ip = df["ip"][i]#获取ip
                db_path = df["db_path"][i]#获取数据库路径
                enabled = df["enabled"][i]#获取是否启用
                access_path = f"\\\{ip}{db_path}\\{date1}-{line}-{shift}.mdb"#线别数据库路径
                print(access_path)
               
                if enabled==0:
                    print(f"第 {i} 行: {line}, 未启用")
                    continue
                else:
                    print(f"第 {i} 行: {line}, 启用")
                     # 初始化 AccessDBManager 实例
                    access_db_manager = AccessDBManager(access_path) 
                    if access_db_manager.connect():
                        try:
                        # 执行 SQL 查询
                            if hour>21 and hour<24:
                                if df["cutover_completed"][i]==1:
                                    print("对切完成")
                                else:
                                    print("对切未完成")
                                    dq = pd.read_sql_query(cfg.access_sql_dq, access_db_manager.conn)
                                    if dq["DQ"][0]>5:
                                        print("对切完成")
                                        df["cutover_completed"][i]=1
                            elif hour>8 and hour<12:
                                if df["cutover_completed"][i]==1:
                                    print("对切完成")
                                else:
                                    print("对切未完成")
                                    dq = pd.read_sql_query(cfg.access_sql_dq, access_db_manager.conn)
                                    if dq["DQ"][0]>5:
                                        print("对切完成")
                                        df["cutover_completed"][i]=1
                            else:
                                pass
                            if hour>20.5 and hour<21.5:
                                xs_opening = pd.read_sql_query(cfg.access_sql_xs_opening, access_db_manager.conn)
                                xs_open_isc=xs_opening["xs_isc"][0].round(5)
                                xs_open_uoc=xs_opening["xs_uoc"][0].round(5)
                                xs_open_ff=xs_opening["xs_ff"][0].round(5)
                                df["opening_isc"][i]=xs_open_isc
                                df["opening_uoc"][i]=xs_open_uoc
                                df["opening_ff"][i]=xs_open_ff
                                df["current_isc"][i]=xs_open_isc
                                df["current_uoc"][i]=xs_open_uoc
                                df["current_ff"][i]=xs_open_ff
                                df["efficiency_impact"][i]=0
                            elif hour>8.5 and hour<9:
                                xs_opening = pd.read_sql_query(cfg.access_sql_xs_opening, access_db_manager.conn)
                                xs_open_isc=xs_opening["xs_isc"][0].round(5)
                                xs_open_uoc=xs_opening["xs_uoc"][0].round(5)
                                xs_open_ff=xs_opening["xs_ff"][0].round(5)
                                df["opening_isc"][i]=xs_open_isc
                                df["opening_uoc"][i]=xs_open_uoc
                                df["opening_ff"][i]=xs_open_ff
                                df["current_isc"][i]=xs_open_isc
                                df["current_uoc"][i]=xs_open_uoc
                                df["current_ff"][i]=xs_open_ff
                                df["efficiency_impact"][i]=0
                            else:
                                xs_current = pd.read_sql_query(cfg.access_sql_xs_current, access_db_manager.conn)
                                xs_current_isc=xs_current["xs_isc"][0].round(5)
                                xs_current_uoc=xs_current["xs_uoc"][0].round(5)
                                xs_current_ff=xs_current["xs_ff"][0].round(5)
                                xs_open_isc=df["opening_isc"][i]
                                xs_open_uoc=df["opening_uoc"][i]
                                xs_open_ff=df["opening_ff"][i]
                                df["current_isc"][i]=xs_current_isc
                                df["current_uoc"][i]=xs_current_uoc
                                df["current_ff"][i]=xs_current_ff
                                df["efficiency_impact"][i]=((xs_current_ff-xs_open_ff)*0.5+(xs_current_uoc-xs_open_uoc)*0.066667-(xs_current_isc-xs_open_isc)*1.25).round(5)
                        except Exception as e:
                            print(f"执行 SQL 查询失败: {e}")
                        finally:
                            # 关闭数据库连接
                            access_db_manager.close()

            except Exception as e:
                print(f"处理第 {i} 行时发生错误: {e}")
        # 将修改后的DataFrame更新到数据库
        df.to_sql('ip_config', db_manager.conn, if_exists='replace', index=False)
        print("------------------")
    except Exception as e:
        print(f"发生错误: {e}")
    finally:
        # 关闭数据库连接
        db_manager.close()
